package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.CashPaymentDetailsDataList;
import com.mbc.rmt.entities.CashPayment;
import com.mbc.rmt.entities.CashPaymentCri;
import com.mbc.rmt.entities.CashPaymentDetails;
import com.mbc.rmt.entities.DefinePaymentTypeDetail;

public class CashPaymentDetailDAOImpls extends DAO<CashPaymentDetails> implements CashPaymentDetailDAO{

	public final String field_slipdetail_id = "slipdetailid";
	public final String field_slip_id = "slipid";
	public final String field_invoice_no = "invoiceno";
	public final String field_receiptno = "receiptno";
	public final String field_sender = "sender";
	public final String field_receiver = "receiver";
	public final String field_address = "address";
	public final String field_currency_id = "currencyid";
	public final String field_currency_code = "currencycode";
	public final String field_currencyrate_code = "currencyratecode";
	public final String field_rate = "rate";
	public final String field_amount = "amount";
	public final String field_applyamount = "applyamount";
	public final String field_orgpaidedamount = "orgpaidamount";
	public final String field_paidedamount = "paidamount";
	public final String field_tran_status = "transtatus";
	public final String field_balance = "balance";
	public final String field_detail_id = "detailid";
	public final String field_definedetail_id = "definedetailid";
	public final String field_type_code = "type_code";
	public final String field_frombranch_id = "frombranchid";
	public final String field_frombranch_code = "frombranchcode";
	public final String field_returnstatus = "returnstatus";
	
	public final String field_receivedbranch_code = "receivedbranchcode";
	public final String field_receivedbranch_id = "receivedbranchid";
	
	enum TRAN_MODE { SAVE, UPDATE }
	
	public CashPaymentDetailDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "cashpaymentdetails";
	}

	@Override
	public ArrayList<CashPaymentDetails> getCashPaymentInfolist(long slipkey)
			throws SQLException {
		ArrayList<CashPaymentDetails> l_result = null;
		query = "DECLARE @homecurrencyid bigint " +
				    "SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				    " SELECT cpd.*,ISNULL(dptd.paidamount,0)orgpaidamount,(dptd.paidamount - cpd.applyamount)paidamount," +
				    "(cpd.amount - dptd.paidamount)balance,c.currencycode," +
				    "(CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=cpd.currencyid" +
				    " THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode," +
				    "b.branchcode frombranchcode,rate FROM cashpaymentdetails cpd " +
				    " inner join CashPayment cp on cp.SlipID = cpd.SlipID" +
				    " INNER JOIN definepaymenttypedetail dptd ON dptd.detailid = cpd.definedetailid" +
				    " LEFT JOIN branch b on b.branchid = cpd.frombranchid" +
				    " INNER JOIN Currency c ON cpd.currencyid=c.currencyid" +
				    " LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cpd.currencyid" +
				    " AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = cp.DefaultBranchID "+
				    " Where cpd.slipid = " + slipkey;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_result = new ArrayList<CashPaymentDetails>();
			do {
				l_result.add(readRecord(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_result;
	}
	
	@Override
	public ArrayList<CashPaymentDetails> getCashPaymentDetaillist(
			CashPayment entity) throws SQLException {
		ArrayList<CashPaymentDetails> l_result = null;
		query = "SELECT dptd.detailid,dptd.InvoiceNo,dptd.ReceiptNo,dptd.sender,dptd.receiver,dptd.address,c.currencyid," +
					"c.currencycode,dpt.ReceivedBranchCode,dpt.ReceivedBranchID,dptd.amount,ISNULL(dptd.paidamount,0)paidamount," +
					"(dptd.amount-ISNULL(dptd.paidamount,0))Balance,t.type_code" +
					" FROM DefinePaymentType dpt" +
					" INNER JOIN  dbo.DefinePaymentTypeDetail dptd ON dpt.InvoiceID = dptd.Invoiceid ";
		if(entity.getTrcode1() != 'S')
			query += " INNER JOIN dbo.Agent a ON a.agent_id = dptd.agentid";
				
		query +=	" INNER JOIN TYPE t ON t.Type_ID = dptd.TypeID" +
					" LEFT JOIN currency c ON c.currencyid = dptd.currencyid" +
					" WHERE (t.type_code = '"+entity.getTrcode1()+"' OR t.type_code = '"+entity.getTrcode2()+"')";
		if(entity.getTrcode1() !='S')
			query += " and a.agent_code = '"+entity.getAgentcode() + "'";
		query +=  " and dpt.branchid = "+ entity.getDefaultbranchID()+
					  " AND ISNULL(dptd.paidamount,0) <> dptd.amount" +
					  " AND isnull(dptd.ReturnStatus,0) <> 1" +
					  " and dptd.CurrencyID = "+entity.getCurrencyID();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_result = new ArrayList<CashPaymentDetails>();
			do {
				l_result.add(readRecords(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_result;
	}
	
	@Override
	public CashPaymentDetailsDataList findCashPaymentDetaillist(CashPaymentCri cri)
			throws SQLException {
		CashPaymentDetailsDataList l_DataList = new CashPaymentDetailsDataList();
		ArrayList<CashPaymentDetails> l_entityList = new ArrayList<CashPaymentDetails>();
		String l_cri = " and dptd."+cri.getFieldname() + " like N'%" + cri.getKeyword() + "%' " +					   
					   " and (dptd.TypeCode = '"+cri.getTrcode1()+"')"+
					   " and c.currencyid = "+cri.getCurrencykey()+
					   " and dpt.branchid = "+ cri.getBranchId()+
					   " AND ISNULL(dptd.paidamount,0) <> dptd.amount" +
					   " And isnull(dptd.ReturnStatus,0) <> 1";
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by dptd.InvoiceNo) as [rowno],dptd.detailid,dptd.InvoiceNo,dptd.ReceiptNo,dptd.sender,dptd.receiver,dptd.address,c.currencyid," +
				"c.currencycode,dpt.ReceivedBranchCode,dpt.ReceivedBranchID,dptd.amount,ISNULL(dptd.paidamount,0)paidamount," +
				"(dptd.amount-ISNULL(dptd.paidamount,0))Balance,dptd.TypeCode As type_code" +
				" FROM DefinePaymentType dpt" +
				" INNER JOIN  dbo.DefinePaymentTypeDetail dptd ON dpt.InvoiceID = dptd.Invoiceid" +
				" INNER JOIN TYPE t ON t.Type_ID = dptd.TypeID" +
				" LEFT JOIN currency c ON c.currencyid = dptd.currencyid " 
				+ " where 1 = 1" + l_cri + ")a"
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecords(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(dptd.invoiceno) from DefinePaymentType dpt" +
				" INNER JOIN  dbo.DefinePaymentTypeDetail dptd ON dpt.InvoiceID = dptd.Invoiceid" +
				" INNER JOIN TYPE t ON t.Type_ID = dptd.TypeID " +
				" LEFT JOIN currency c ON c.currencyid = dptd.currencyid " +
				"where 1=1 "
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public void create(CashPaymentDetails infodetail) throws SQLException {
		query = insertParam(table_name, new String[] { field_slipdetail_id, field_slip_id,
				field_invoice_no ,field_receiptno ,field_sender, field_receiver,field_address,
				field_currency_id, field_rate ,field_amount,field_applyamount,field_definedetail_id,field_type_code,field_frombranch_id, field_returnstatus });
		
		pstmt = conn.prepareStatement(query);
		updateRecord(infodetail, TRAN_MODE.SAVE);
		pstmt.executeUpdate();
		pstmt.close();
	}	

	@Override
	public void update(CashPaymentDetails infodetail) throws SQLException {
		query = updateParam(table_name, new String[] { field_slipdetail_id, field_slip_id,
				field_invoice_no ,field_receiptno ,field_sender, field_receiver,field_address,
				field_currency_id, field_rate ,field_amount,field_applyamount,field_definedetail_id,field_type_code,field_frombranch_id, field_returnstatus })
				+ " AND " + field_slipdetail_id + " = " + infodetail.getSlipdetailID();				
		pstmt = conn.prepareStatement(query);
		updateRecord(infodetail, TRAN_MODE.UPDATE);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void delete(long key) throws SQLException {
		query = "DELETE FROM "+table_name+" WHERE "+field_slip_id +" = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}
	

	@Override
	public void deleteRow(long key) throws SQLException {
		query = "DELETE FROM "+table_name+" WHERE "+field_slipdetail_id +" = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();		
	}

	
	@Override
	public void updateTranStatusDPD(DefinePaymentTypeDetail infodetail)
			throws SQLException {
		query = updateParam("definepaymenttypedetail", new String[] { field_paidedamount,field_returnstatus,field_tran_status}) +
				" AND " + field_detail_id + " = " + infodetail.getDetailId();
		
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setDouble(i++, infodetail.getPaidamount());
		if(infodetail.getReturnstatus() == null)
			pstmt.setByte(i++,(byte)0);
		else
			pstmt.setByte(i++,infodetail.getReturnstatus());
		pstmt.setByte(i++, infodetail.getTranStatus());
		pstmt.executeUpdate();
		pstmt.close();
		
	}
	
/*	@Override
	public CashPaymentDetails find(long key) throws SQLException {
		CashPaymentDetails cashpaymentdetial = new CashPaymentDetails();
		query = "select definedetailid,applyamount from cashpaymentdetails where definedetailid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()){
			cashpaymentdetial.setSlipdetailID(rs.getLong(field_definedetail_id));
			cashpaymentdetial.setApplyamt(rs.getDouble(field_applyamount));
		}
		rs.close();
		pstmt.close();
		return cashpaymentdetial;
	}*/
	
	/*@Override
	public CashPaymentDetails find(long key) throws SQLException {
		CashPaymentDetails cashpaymentdetial = new CashPaymentDetails();
		query = "select definedetailid,applyamount from cashpaymentdetails where definedetailid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()){
			cashpaymentdetial.setSlipdetailID(rs.getLong(field_definedetail_id));
			cashpaymentdetial.setApplyamt(rs.getDouble(field_applyamount));
		}
		rs.close();
		pstmt.close();
		return cashpaymentdetial;
	}*/
	
	@Override
	public CashPaymentDetails find(long slipdetailkey,long definedetailikey) throws SQLException {
		CashPaymentDetails cashpaymentdetial = new CashPaymentDetails();
		String l_cri = "";
		if(slipdetailkey != 0)
			l_cri = " and "+field_slipdetail_id +"="+slipdetailkey;
		else if(definedetailikey != 0)
			l_cri = " and "+field_definedetail_id+"="+definedetailikey;
		
		query = "select definedetailid,applyamount from cashpaymentdetails where 1=1 " + l_cri;//"select paidamount from definepaymenttypedetail where detailid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()){
			cashpaymentdetial.setDptdetailid(rs.getLong(field_definedetail_id));
			cashpaymentdetial.setApplyamt(rs.getDouble(field_applyamount));
		}
		rs.close();
		pstmt.close();
		return cashpaymentdetial;
	}
	
	@Override
	protected CashPaymentDetails readRecord(ResultSet rs) throws SQLException {
		CashPaymentDetails entity = new CashPaymentDetails();
		entity.setSlipdetailID(rs.getLong(field_slipdetail_id));
		entity.setSlipID(rs.getLong(field_slip_id));
		entity.setInvoiceNo(rs.getString(field_invoice_no));
		entity.setReceiptNo(rs.getString(field_receiptno));
		entity.setSender(rs.getString(field_sender));
		entity.setReceiver(rs.getString(field_receiver));
		entity.setAddress(rs.getString(field_address));
		entity.setCurrencyID(rs.getLong(field_currency_id));
		entity.setCurrencycode(rs.getString(field_currency_code));
		entity.setCurrencyratecode(rs.getString(field_currencyrate_code));
		entity.setRate(rs.getDouble(field_rate));
		entity.setAmount(rs.getDouble(field_amount));
		entity.setApplyamt(rs.getDouble(field_applyamount));
		entity.setDptdetailid(rs.getLong(field_definedetail_id));
		entity.setType_code(rs.getString(field_type_code));
		entity.setFrombranchid(rs.getLong(field_frombranch_id));
		entity.setFrombranchcode(rs.getString(field_frombranch_code));
		entity.setOrgpaidamount(rs.getDouble(field_orgpaidedamount));
		entity.setPaidedamount(rs.getDouble(field_paidedamount));
		entity.setBalance(rs.getDouble(field_balance));
		entity.setReturnStatus(rs.getByte(field_returnstatus));
		return entity;
	}	
	
	protected CashPaymentDetails readRecords(ResultSet rs)throws SQLException 
	{
		CashPaymentDetails entity = new CashPaymentDetails();		
		entity.setDptdetailid(rs.getLong(field_detail_id));
		entity.setInvoiceNo(rs.getString(field_invoice_no));
		entity.setReceiptNo(rs.getString(field_receiptno));
		entity.setSender(rs.getString(field_sender));
		entity.setReceiver(rs.getString(field_receiver));
		entity.setAddress(rs.getString(field_address));
		entity.setCurrencyID(rs.getLong(field_currency_id));
		entity.setCurrencycode(rs.getString(field_currency_code));
		entity.setAmount(rs.getDouble(field_amount));
		entity.setPaidedamount(rs.getDouble(field_paidedamount));
		entity.setBalance(rs.getDouble(field_balance));
		entity.setType_code(rs.getString(field_type_code));
		entity.setFrombranchid(rs.getLong(field_receivedbranch_id));
		entity.setFrombranchcode(rs.getString(field_receivedbranch_code));
		return entity;
	}
	
	protected void updateRecord(CashPaymentDetails entity, TRAN_MODE mode) throws SQLException {
		int i = 1;
		pstmt.setLong(i++, entity.getSlipdetailID());
		pstmt.setLong(i++, entity.getSlipID());
		pstmt.setString(i++, entity.getInvoiceNo());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setLong(i++, entity.getCurrencyID());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getAmount());
		pstmt.setDouble(i++, entity.getApplyamt());
		pstmt.setLong(i++, entity.getDptdetailid());
		pstmt.setString(i++, entity.getType_code());
		if(entity.getFrombranchid()!=null)
			pstmt.setLong(i++, entity.getFrombranchid());
		else
			pstmt.setString(i++, null);
		
		if(mode == TRAN_MODE.SAVE)
			pstmt.setByte(i++, (byte)0);
		else
			pstmt.setByte(i++, entity.getReturnStatus());
	}

	@Override
	public CashPaymentDetails finddefinepaidamt(long key) throws SQLException {
		CashPaymentDetails cashpaymentdetial = new CashPaymentDetails();
		query = "select isnull(paidamount,0)paidamount  from definepaymenttypedetail where detailid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			cashpaymentdetial.setPaidedamount(rs.getDouble(field_paidedamount));
		rs.close();
		pstmt.close();
		return cashpaymentdetial;
	}
	
}
